package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.fxmargin.bo.ListOfForwardContractBO;

public class ListOfForwardContractDAO {
	public ListOfForwardContractBO getForwardContractList(ListOfForwardContractBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<ListOfForwardContractBO> resultList = null;
		ListOfForwardContractBO fc = null;
		int rowNum = 0;
		
		try{
			sql = "with param as (select ? cif_no, ? as_of_date)"+
					" select d.id " +
					" , d.cif_no" +
					" , d.deal_id" +
					" , DATEDIFF(DAY, p.as_of_date, d.value_date) day_to_mat" +
					" , d.value_date mat_date"+
					" , case d.buy_sell"+
					" when 'S' then 'Sell'"+
					" when 'B' then 'Buy'"+
					" end buy_sell"+ 
					" , d.ccy1+d.ccy2 ccy_pair" +
					" , d.exch_rate fx_rate" +
					" , d.deal_amt1 amt1" +
					" , d.equity_amt amt2" +
					" , d.im_required" +
					" , d.mtm_rate" +
					" , d.unrealized_pl"+
					" , d.closed_rate" +
					" , d.realized_pl" +
					" , d.doc_type deal_status" +
					" , d.trans_type deal_type" +
					" from t_deals_history d, param p"+ 
					" where d.active_status='A'"+
					" and d.cif_no=p.cif_no"+
					" and d.as_of_date = p.as_of_date "+
					" order by d.cif_no, d.deal_id";
			
			System.out.println(sql);
			System.out.println(">param1: "+bo.getInCifNo());
			System.out.println(">param2: "+bo.getInAsOfDate());
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bo.getInCifNo());
			pstmt.setString(2, bo.getInAsOfDate());
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<ListOfForwardContractBO>();
				
				while(rs.next()){
					fc = new ListOfForwardContractBO();
					fc.setId(rs.getString("id"));
					fc.setCifNo(rs.getString("cif_no"));
					fc.setDealId(rs.getString("deal_id"));
					fc.setDayToMat(rs.getString("day_to_mat"));
					fc.setMatDate(rs.getString("mat_date"));
					fc.setBuySell(rs.getString("buy_sell"));
					fc.setCcyPair(rs.getString("ccy_pair"));
					fc.setFxRate(rs.getString("fx_rate"));
					fc.setAmt1(rs.getString("amt1"));
					fc.setAmt2(rs.getString("amt2"));
					fc.setImRequired(rs.getString("im_required"));
					fc.setMtmRate(rs.getString("mtm_rate"));
					fc.setUnrealizedPL(rs.getString("unrealized_pl"));
					fc.setCloseRate(rs.getString("closed_rate"));
					fc.setRealizedPL(rs.getString("realized_pl"));
					fc.setDealStatus(rs.getString("deal_status"));
					fc.setDealType(rs.getString("deal_type"));
					fc.setRowNum(++rowNum);
					resultList.add(fc);
				}
				
				bo.setForwardContractList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
}
